package com.cn.wanxi.dao.impl;

import com.cn.wanxi.dao.IUserDao;
import com.cn.wanxi.model.UserModel;
import com.cn.wanxi.util.JDBC;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserImpl implements IUserDao {
    @Override
    public int add(UserModel userModel) {
//        1.封装sql语句
        String sql = "insert into user(username,password,phone,sex,birthday,hobby,introduce) " +
                "values('" + userModel.getUsername() + "'," +
                "'" + userModel.getPassword() + "'," +
                "'" + userModel.getPhone() + "'," +
                "'" + userModel.getSex() + "'," +
                "'" + userModel.getBirthday() + "'," +
                "'" + userModel.getHobby() + "'," +
                "'" + userModel.getIntroduce() + "')";
//        2.执行sql语句
        int count = JDBC.excuteUpdate(sql);
//        3.返回数据结果
        return count;
    }

    @Override
    public UserModel findByUsername(String username) {
        String sql = "select * from user where username='" + username + "'";
        UserModel userModel = null;
        ResultSet resultSet = JDBC.getResultSet(sql);//根据sql得到数据库查询的结果集
        try {
            while (resultSet.next()) {
                userModel = new UserModel();
//                userModel.setUsername(resultSet.getString("username"));
            }
        } catch (Exception e) {
            System.out.println("数据查询错误");
        }
        return userModel;
    }

    @Override
    public UserModel isHaveFindByUsernameAndPassword(UserModel userModel) {
        String sql = "select * from user where username='" +
                userModel.getUsername() + "' and password='" +
                userModel.getPassword() + "'";
        UserModel model = null;
        ResultSet resultSet = JDBC.getResultSet(sql);
        try {
            while (resultSet.next()) {
                model = new UserModel();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return model;
    }

    @Override
    public List<UserModel> findAll() {
        String sql = "select * from user";
        List<UserModel> list = new ArrayList<>();
        ResultSet resultSet = JDBC.getResultSet(sql);
        try {
            while (resultSet.next()) {
                UserModel userModel = new UserModel();
                userModel.setId(resultSet.getInt("id"));
                userModel.setUsername(resultSet.getString("username"));
                userModel.setBirthday(resultSet.getString("birthday"));
                userModel.setIntroduce(resultSet.getString("introduce"));
                userModel.setHobby(resultSet.getString("hobby"));
                userModel.setSex(resultSet.getString("sex"));
                userModel.setPhone(resultSet.getString("phone"));
                list.add(userModel);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public int delete(Integer id) {
        String sql = "delete from user where id=" + id;
        int count = JDBC.excuteUpdate(sql);
        return count;
    }

    @Override
    public List<UserModel> findAllBySome(UserModel userModel) {
        StringBuilder sql = new StringBuilder("select * from user where 1=1");
        if (!"".equals(userModel.getUsername())) {
            sql.append(" and username like '%").append(userModel.getUsername()).append("%' ");
        }
//        生日
        if ("".equals(userModel.getStartBirthday()) && !"".equals(userModel.getEndBirthday())) {
            sql.append(" and  DATE_FORMAT(birthday,'%Y-%m-%d') <'").append(userModel.getEndBirthday()).append("' ");
        }
        if (!"".equals(userModel.getStartBirthday()) && "".equals(userModel.getEndBirthday())) {
            sql.append(" and  DATE_FORMAT(birthday,'%Y-%m-%d') >'").append(userModel.getStartBirthday()).append("' ");
        }
        if (!"".equals(userModel.getStartBirthday()) && !"".equals(userModel.getEndBirthday())) {
            sql.append(" and DATE_FORMAT(birthday,'%Y-%m-%d') between '").append(userModel.getStartBirthday()).append("' and '").append(userModel.getEndBirthday()).append("' ");
        }

        if (!"".equals(userModel.getIntroduce())) {
            sql.append(" and introduce like '%").append(userModel.getIntroduce()).append("%' ");
        }
        if (!"[]".equals(userModel.getHobby())) {
            String[] hb = userModel.getHobby().replace("[", "").replace("]", "").split(",");

            for (int i = 0; i < hb.length; i++) {
                if (i == 0) {
                    sql.append(" and FIND_IN_SET(").append(hb[i].trim()).append(",hobby)");
                } else {
                    sql.append(" or FIND_IN_SET(").append(hb[i].trim()).append(",hobby)");
                }

            }
        }
        if (!"全部".equals(userModel.getSex())) {
            sql.append(" and sex='").append(userModel.getSex()).append("' ");
        }
        if (!"".equals(userModel.getPhone())) {
            sql.append(" and phone like '%").append(userModel.getPhone()).append("%'");
        }
        sql.append(" limit ").append((userModel.getPageModel().getPageNum() - 1) * userModel.getPageModel().getPageSize()).append(",").append(userModel.getPageModel().getPageSize());
        List<UserModel> list = new ArrayList<>();
        ResultSet resultSet = JDBC.getResultSet(sql.toString());
        try {
            while (resultSet.next()) {
                UserModel model = new UserModel();
                model.setId(resultSet.getInt("id"));
                model.setUsername(resultSet.getString("username"));
                model.setBirthday(resultSet.getString("birthday"));
                model.setIntroduce(resultSet.getString("introduce"));
                model.setHobby(resultSet.getString("hobby"));
                model.setSex(resultSet.getString("sex"));
                model.setPhone(resultSet.getString("phone"));
                list.add(model);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public UserModel findById(Integer id) {
        String sql = "select * from user where id=" + id;
        ResultSet resultSet = JDBC.getResultSet(sql);
        UserModel userModel = new UserModel();
        try {
            while (resultSet.next()) {
                userModel.setId(resultSet.getInt("id"));
                userModel.setUsername(resultSet.getString("username"));
                userModel.setBirthday(resultSet.getString("birthday"));
                userModel.setIntroduce(resultSet.getString("introduce"));
                userModel.setHobby(resultSet.getString("hobby"));
                userModel.setSex(resultSet.getString("sex"));
                userModel.setPhone(resultSet.getString("phone"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return userModel;
    }

    @Override
    public int edit(UserModel userModel) {
        StringBuilder sql = new StringBuilder("update user set ");
        if (userModel.getUsername() != "") {
            sql.append(" username='" + userModel.getUsername() + "',");
        }
        if (userModel.getPassword() != "") {
            sql.append(" password='" + userModel.getPassword() + "',");
        }
        sql.append("sex = '" + userModel.getSex() + "',");
        sql.append("birthday = '" + userModel.getBirthday() + "',");
        sql.append("hobby = '" + userModel.getHobby() + "',");
        sql.append("introduce = '" + userModel.getIntroduce() + "',");
        sql.append("phone = '" + userModel.getPhone() + "',");
        sql.append(" remark='' where id=" + userModel.getId());
        return JDBC.excuteUpdate(sql.toString());
    }

    @Override
    public int getCount(UserModel userModel) {
        StringBuilder sql = new StringBuilder("select count(*) as count from user where 1=1");
        if (!"".equals(userModel.getUsername())) {
            sql.append(" and username like '%").append(userModel.getUsername()).append("%' ");
        }
//        生日
        if ("".equals(userModel.getStartBirthday()) && !"".equals(userModel.getEndBirthday())) {
            sql.append(" and  DATE_FORMAT(birthday,'%Y-%m-%d') <'").append(userModel.getEndBirthday()).append("' ");
        }
        if (!"".equals(userModel.getStartBirthday()) && "".equals(userModel.getEndBirthday())) {
            sql.append(" and  DATE_FORMAT(birthday,'%Y-%m-%d') >'").append(userModel.getStartBirthday()).append("' ");
        }
        if (!"".equals(userModel.getStartBirthday()) && !"".equals(userModel.getEndBirthday())) {
            sql.append(" and DATE_FORMAT(birthday,'%Y-%m-%d') between '").append(userModel.getStartBirthday()).append("' and '").append(userModel.getEndBirthday()).append("' ");
        }

        if (!"".equals(userModel.getIntroduce())) {
            sql.append(" and introduce like '%").append(userModel.getIntroduce()).append("%' ");
        }
        if (!"[]".equals(userModel.getHobby())) {
            String[] hb = userModel.getHobby().replace("[", "").replace("]", "").split(",");

            for (int i = 0; i < hb.length; i++) {
                if (i == 0) {
                    sql.append(" and FIND_IN_SET(").append(hb[i].trim()).append(",hobby)");
                } else {
                    sql.append(" or FIND_IN_SET(").append(hb[i].trim()).append(",hobby)");
                }

            }
        }
        if (!"全部".equals(userModel.getSex())) {
            sql.append(" and sex='").append(userModel.getSex()).append("' ");
        }
        if (!"".equals(userModel.getPhone())) {
            sql.append(" and phone like '%").append(userModel.getPhone()).append("%'");
        }

        ResultSet resultSet = JDBC.getResultSet(sql.toString());
        int count=0;
        try {
            while (resultSet.next()) {
               count=resultSet.getInt("count");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }
}
